using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using DTO;

/// Quang

namespace DAO
{
    public  class DonViDuocPhamDao:IDaoObject<DonViDuocPhamDto,int>
    {
        public DonViDuocPhamDto SaveOrUpdate(DonViDuocPhamDto obj)
        {
            string strPattern = "";
            string strSql = "";
            int id = 0;
            if (obj.Id > 0 || CheckAlready(obj.TenDonVi, out id))
            {
                if (obj.Id <= 0) obj.Id = id;
                return obj;
            }else
            {
                //insert into table
                strPattern = "insert into Donviduocpham (Tendonvi) values ('{0}')";
                strSql = string.Format(strPattern, obj.TenDonVi);
            }
            OleDbConnection cn = MyConnection.ConnectionData();
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            //obj.Id = cmd.ExecuteNonQuery(); //tra ve 1 neu insert thanh cong, tra ve 0 neu insert that bai
            cmd.ExecuteNonQuery();
            //lay Id lon nhat(Id cua doi tuong moi vua them vao)
            strSql = "Select @@IDENTITY";     
            cmd = new OleDbCommand(strSql, cn);
            obj.Id = (int)cmd.ExecuteScalar();
            cn.Close();
            return obj;
        }

        public DonViDuocPhamDto SaveOrUpdate(DonViDuocPhamDto obj, OleDbTransaction transaction)
        {
            OleDbCommand cmd = new OleDbCommand();
            if (transaction != null)
            {
                cmd.Connection = transaction.Connection;
                cmd.Transaction = transaction;
            }
            else
            {
                cmd.Connection = MyConnection.ConnectionData();
            }
            string strPattern = "";
            string strSql = "";
            int id = 0;
            if (obj.Id > 0 || CheckAlready(obj.TenDonVi,out id))
            {
                //update table
                //strPattern = "update Donviduocpham set Tendonvi='{0}' where ID={1}";
                //strSql = string.Format(strPattern, obj.TenDonVi, obj.Id);
                if (obj.Id <= 0) obj.Id = id;
                return obj;
            }
            else
            {
                //insert into table
                strPattern = "insert into Donviduocpham (Tendonvi) values ('{0}')";
                strSql = string.Format(strPattern, obj.TenDonVi);
            }
            OleDbConnection cn = transaction.Connection;
            
            cmd.Transaction = transaction;
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();
            //lay Id lon nhat(Id cua doi tuong moi vua them vao)
            strSql = "Select @@IDENTITY";
            cmd.CommandText = strSql;
            cmd.Transaction = transaction;
            obj.Id = (int)cmd.ExecuteScalar();
            //cn.Close();
            return obj;
        }

        public void Delete(int id)
        {
            const string strPattern = "delete from Donviduocpham ID = {0}";
            string strSql = string.Format(strPattern, id);
            
            OleDbConnection cn = MyConnection.ConnectionData();
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public DonViDuocPhamDto Select(int id)
        {
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID,Tendonvi from Donviduocpham where ID=" + id;
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            DataRow row = dataTable.Rows[0];
            DonViDuocPhamDto donViDuocPhamDto = new DonViDuocPhamDto();
            donViDuocPhamDto.Id = Convert.ToInt32(row["ID"].ToString());
            donViDuocPhamDto.TenDonVi = (string)row["Tendonvi"];
            return donViDuocPhamDto;
        }
        public bool CheckAlready(string name, out int id)
        {
            id = 0;
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID from Donviduocpham where Tendonvi='" + name + "'";
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            if( dataTable.Rows.Count > 0)
            {
                id = Convert.ToInt32(dataTable.Rows[0]["ID"].ToString());
            }
            return id > 0;
        }
        public DonViDuocPhamDto[] SelectAll()
        {
            OleDbConnection con = MyConnection.ConnectionData();
            const string sql = "select ID,Tendonvi from Donviduocpham ";
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List<DonViDuocPhamDto> list = new List<DonViDuocPhamDto>();
            foreach (DataRow row in dataTable.Rows)
            {
                DonViDuocPhamDto donViDuocPhamDto = new DonViDuocPhamDto();
                donViDuocPhamDto.Id = Convert.ToInt32(row["ID"].ToString());
                donViDuocPhamDto.TenDonVi = (string)row["Tendonvi"];
                list.Add(donViDuocPhamDto);
            }
            return list.ToArray();
        }
    }
}
